package com.egg.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.egg.common.Page;

public class BaseDao extends JdbcDaoSupport{
	public static final Log log = LogFactory.getLog(BaseDao.class);

	public int queryForInt(String sql) {
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().queryForInt(sql);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = 0;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		return ret;
	}

	public int queryForInt(String sql, Object... obj) {
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().queryForInt(sql, obj);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = 0;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		return ret;
	}

	public long queryForLong(String sql) {
		long ret = 0;
		try {
			ret = this.getJdbcTemplate().queryForLong(sql);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = 0;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		return ret;
	}

	public Object queryForObject(String sql, Class<?> requiredType) {

		Object ret = null;
		try {
			ret = this.getJdbcTemplate()
					.queryForObject(sql, requiredType);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = null;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		return ret;
	}

	public Object queryForObject(String sql, Class<?> requiredType,
			Object... obj) {

		Object ret = null;
		try {
			ret = this.getJdbcTemplate().queryForObject(sql,
					requiredType, obj);
		} catch (IncorrectResultSizeDataAccessException e1) {
			ret = null;
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		return ret;
	}

	public Map<String, Object> queryForMap(String sql) {

		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		if (row == null || row.size() == 0) {
			return null;
		}

		return row.get(0);
	}

	public Map<String, Object> queryForMap(String sql, Object... obj) {
		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql, obj);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		if (row == null || row.size() == 0) {
			return null;
		}

		return row.get(0);
	}

	public List<Map<String, Object>> queryForList(String sql) {
		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		return row;
	}

	public List<Map<String, Object>> queryForList(String sql, Object... obj) {
		List<Map<String, Object>> row = null;

		try {
			row = this.getJdbcTemplate().queryForList(sql, obj);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}

		return row;
	}

	public int update(String sql) {
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().update(sql);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}
		return ret;
	}

	public int update(String sql, Object... obj) {
		int ret = 0;
		try {
			ret = this.getJdbcTemplate().update(sql, obj);
		} catch (DataAccessException e2) {
			SQLException sqle = (SQLException) e2.getCause();
			log.error(e2.getMessage());
			log.error("ErrorCode:" + sqle.getErrorCode() + " SQLState:"
					+ sqle.getSQLState());
			throw e2;
		}
		return ret;
	}

	/**
	 *
	 * @Title: callProcString
	 * @Description:
	 * @param @param storedProc
	 * @param @param outtype
	 * @param @param parameters
	 * @param @return
	 * @return Object
	 * @throws
	 */
	public String callProcString(String storedProc, int outtype,
			Object... parameters) {
		String procedure = "{call " + storedProc + "(";
		for (int i = 0; i < parameters.length; i++) {
			procedure += "?,";
		}
		storedProc = procedure + "?)}";
		return callProc(storedProc, outtype, parameters.length + 1, parameters);
	}

	private String callProc(final String storedProc, final int outtype,
			final int outindex, final Object... parameters) {
		String ret = this.getJdbcTemplate().execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con.prepareCall(storedProc);
				int i = 0;
				for (; i < parameters.length; i++) {
					cs.setObject(i + 1, parameters[i]);
				}
				cs.registerOutParameter(i + 1, outtype);// 注册输出参数的类型
				return cs;
			}
		}, new CallableStatementCallback<String>() {
			public String doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				return cs.getObject(outindex).toString();// 获取输出参数的值
			}
		});
		return ret;
	}

	public String getCountSql(String sql) {
		StringBuffer str = new StringBuffer(200);
		str.append("select count(*) from ( ").append(sql).append(" ) t");
		return str.toString();
	}

	public String getPageSql(Page page, String sql) {
		StringBuffer str = new StringBuffer(200);
		str.append("SELECT * FROM (SELECT A.*, ROWNUM RN FROM ( ");
		str.append(sql);
		str.append(" ) A WHERE ROWNUM <= ").append(
				page.getPageNo() * page.getPageSize());
		str.append(" ) WHERE RN > ").append(page.getStart());
		if (log.isDebugEnabled()) {
			log.debug("分页查询语句：" + str.toString());
		}
		return str.toString();
	}

	public List<Map<String, Object>> pageQuery(String sql, Page page,
			Object... args) {
		List<Map<String, Object>> row = null;
		int totalCount = queryForInt(getCountSql(sql), args);
		page.setTotalCount(totalCount);
		if (totalCount < 1) {
			row = Collections.emptyList();
		} else if (totalCount <= page.getPageSize()) {
			row = queryForList(sql, args);
		} else {
			row = queryForList(getPageSql(page, sql), args);
		}
		return row;
	}

	public String getTableId(String name){
		StringBuilder name1=new StringBuilder();
		name1.append("SEQ_");
		name1.append(name);
		name1.append(".nextval");
		Map<String, Object> map=this.queryForMap("select "+name1+" from dual");
		return map.get("NEXTVAL").toString();
	}

}
